package com.ticket;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;
import java.util.regex.Pattern;

public class UserSelect {
    private static int count;

    String url = "jdbc:mysql://localhost:3306/ticket?serverTimezone=GMT&useSSL=false";
    String user = "root";
    String password = "123456";

    Connection conn = null;
    ResultSet resultSet = null;
    PreparedStatement pre = null;


//    public static int Input(int num) throws SQLException, ClassNotFoundException {
//        Scanner scanner = new Scanner(System.in);
//        while(true){
//            try{
//                num = scanner.nextInt();
//                return num;
//            }catch (Exception e){
//                System.out.println("输入格式错误,请重新输入");
//                select();
//            }
//        }
//    }
    public static void select() throws SQLException, ClassNotFoundException {
        while (true) {
            System.out.println("通过姓名查找输入1\t\t通过地址查找输入2\n通过id号查找输入3\t\t通过时间查询输入4\n输入-1结束");
            Scanner scanner = new Scanner(System.in);
           String i =  scanner.next();
            if (i.equals("1")) {
                SelectByName();
//                break;
            } else if (i.equals("2")) {
                SelectByAddress();
//                break;
            } else if (i.equals("3")) {
                SelectById();
//                break;
            } else if (i.equals("4")) {
                SelectByDate();
            } else if (i.equals("-1")) {
                System.out.println("查询结束~");
                break;
            } else {
                System.out.println("您输入的数字不正确，请重新输入");
            }
        }


    }

    private static void Gy() {  //在不需要连接时释放相应资源
        UserSelect userSelect = new UserSelect();
        try {
            if (userSelect.resultSet != null) {
                userSelect.resultSet.close();

            }
            if (userSelect.pre != null) {
                userSelect.pre.close();
            }
            if (userSelect.conn != null) {
                userSelect.conn.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void SelectByName() {//通过主唱姓名查找演唱会信息
        //数据库URL,用户名和密码
        UserSelect userSelect = new UserSelect(); //建立UserSelect对象，调用其里面的属性
        try {
            //加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
//            System.out.println("成功加载到MySql驱动");
            //建立连接
            userSelect.conn = DriverManager.getConnection
                    (userSelect.url, userSelect.user, userSelect.password);
//            System.out.println("数据库连接成功");
            String sql = "select * from concert where name = ?";
            //创建PreparedStatement
            userSelect.pre = userSelect.conn.prepareStatement(sql);
            Scanner scanner = new Scanner(System.in);
            while (true) {
                System.out.println("请输入需要查询的明星演唱会(输入-1结束查询)");
                String i="";
                i = scanner.nextLine();
                userSelect.pre.setString(1, i);
                userSelect.resultSet = userSelect.pre.executeQuery();//执行查询
//                if(!userSelect.resultSet.next()){
//                    System.out.print("无此查询，请重新输入");
//                }
                //将输入结果进行比对，如果输入结果为-1则返回上一级
                if (i.equals("-1")) {
                    System.out.println("此次查询结束");
                    break;
                }
                if(!userSelect.resultSet.next()){
                    System.out.print("无此查询，请重新输入");
                }else {
                    //处理查询结果集
                    do {
                        int id = userSelect.resultSet.getInt("id");
                        String name = userSelect.resultSet.getString("name");
                        String address = userSelect.resultSet.getString("address");
                        int total = userSelect.resultSet.getInt("total");
                        int number = userSelect.resultSet.getInt("number");
                        double price = userSelect.resultSet.getDouble("price");
                        Date date = userSelect.resultSet.getDate("date");
                        System.out.println("ID:" + id + ",姓名：" + name + ",演唱会地址：" + address +
                                ",总票数：" + total + ",剩余票数:" + number + "\n开始日期：" + date+",门票价格："+price);
                    }while (userSelect.resultSet.next());
                }



            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Gy();//调用释放资源函数
        }
    }

    public static void SelectById() throws SQLException, ClassNotFoundException { //通过id查找演唱会信息
        //数据库URL,用户名和密码
        UserSelect userSelect = new UserSelect();
            //加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
//            System.out.println("成功加载到MySql驱动");
            //建立连接
            userSelect.conn = DriverManager.getConnection(userSelect.url, userSelect.user, userSelect.password);
//            System.out.println("数据库连接成功");
            String sql = "select * from concert where id = ?";
            //创建prepareStatement
            userSelect.pre = userSelect.conn.prepareStatement(sql);

            Scanner scanner = new Scanner(System.in);

            while (true) {
                System.out.println("请输入需要查询的明星演唱会id号(输入-1结束查询)");
                String i = scanner.next();
                scanner.nextLine();
                if (i.equals("-1")) {
                    System.out.println(i);
                    System.out.println("此次查询结束");
                    break;
                }
                boolean isNotNumber = !Pattern.matches("\\d+", i);
                try {
                    int num = Integer.parseInt(i);
                    userSelect.pre.setInt(1, num);
                    userSelect.resultSet = userSelect.pre.executeQuery();//执行查询
//                count=i;
                    //处理查询结果集
                    if (userSelect.resultSet.next()) {
                        int id = userSelect.resultSet.getInt("id");
                        String name = userSelect.resultSet.getString("name");
                        String address = userSelect.resultSet.getString("address");
                        int total = userSelect.resultSet.getInt("total");
                        int number = userSelect.resultSet.getInt("number");
                        Date date = userSelect.resultSet.getDate("date");
                        double price = userSelect.resultSet.getDouble("price");
                        System.out.println("ID:" + id + ",姓名：" + name + ",演唱会地址：" + address +
                                ",总票数：" + total + ",剩余票数:" + number + "\n开始日期：" + date+",门票价格："+price);


                    } else {
                        System.out.println("此ID号不存在，请重新输入");
                    }
                } catch (Exception e) {
                    System.out.println("您的输入有误");
                } finally {
                    Gy(); //调用函数释放资源
                }
            }
        }

    public static void SelectByAddress(){ //通过地址查找演唱会信息
        //创建UserSelect对象
        UserSelect userSelect = new UserSelect();
        List<List<String>> results = new ArrayList<>(); //创建List集合，存放多条查询结果
        try {
            //加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
//            System.out.println("成功加载到MySql驱动");
            //建立连接
            userSelect.conn = DriverManager.getConnection(userSelect.url, userSelect.user, userSelect.password);
            String sql = "select * from concert where address = ?";
            userSelect.pre = userSelect.conn.prepareStatement(sql);

            Scanner scanner = new Scanner(System.in);
            while (true) {
                System.out.println("请输入需要查询的明星演唱会地址(输入-1结束查询)");
                String i = scanner.next();
//                设置查询参数
                userSelect.pre.setString(1, i);
//                执行查询
                userSelect.resultSet = userSelect.pre.executeQuery();
//                count=i;
//                获取结果集的元数据
                ResultSetMetaData rsmd = userSelect.resultSet.getMetaData();
                //获取列数
                int columnCount = rsmd.getColumnCount();

//                添加列名
                List<String> columNames = new ArrayList<>();
//                for (int j = 1; j <= columnCount; j++) {
//                    columNames.add(rsmd.getColumnName(j));
//                }
                columNames.add("id号");
                columNames.add("演唱者姓名");
                columNames.add("演唱会开始日期");
                columNames.add("演唱会地址");
                columNames.add("总票数");
                columNames.add("剩余票数");
                columNames.add("价格");

                results.add(columNames);
                if (i.equals("-1")) {
                    System.out.println("此次查询结束");
                    break;
                }
                if(!userSelect.resultSet.next()){
                    System.out.print("无此查询");
                    columNames.clear();
                }else {
                //处理查询结果集
                 do{
                    List<String> row = new ArrayList<>();
                    for (int j = 1; j <= columnCount; j++) {
                        row.add(userSelect.resultSet.getString(j)+'\t');
                    }
                    results.add(row);
                }while(userSelect.resultSet.next());
                }

                for (List<String> row : results) {
                    for(String str : row) {
                        System.out.print(str + "\t\t");
                    }
                    System.out.println();
                }
                results.clear();


            }
//
        }catch (Exception e){
            e.printStackTrace();
        } finally{
            Gy();
        }

    }

    public static void SelectByDate() throws SQLException, ClassNotFoundException { //通过日期时间查找演唱会信息
        //数据库URL,用户名和密码
        UserSelect userSelect = new UserSelect();
        List<List<String>> results = new ArrayList<>();
            //加载MySQL JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //建立连接
            userSelect.conn = DriverManager.getConnection(userSelect.url, userSelect.user, userSelect.password);
//            System.out.println("数据库连接成功");
            String sql = "select * from concert where date between ? and ?";
            //创建PreparedStatement对象
            userSelect.pre = userSelect.conn.prepareStatement(sql);

            while (true) {
                System.out.println("请输入需要查询演唱会的日期(输入格式为YYYY-MM-DD)(查询结果为输入日期之后的演唱会)\n(输入-1结束查询)");
                Scanner scanner = new Scanner(System.in);
                System.out.println("请输入开始日期");
                String i = scanner.nextLine();
                if(i.equals("-1")){
                    System.out.println("查询结束");
                    break;
                }
                System.out.println("请输入结束日期");
                try{
                String x = scanner.nextLine();


                userSelect.pre.setDate(1, java.sql.Date.valueOf(i));
                userSelect.pre.setDate(2, java.sql.Date.valueOf(x));
                //执行查询
                userSelect.resultSet = userSelect.pre.executeQuery();
                //                获取结果集的元数据
                ResultSetMetaData rsmd = userSelect.resultSet.getMetaData();
                //获取列数
                int columnCount = rsmd.getColumnCount();

//                添加列名
                List<String> columNames = new ArrayList<>();
//                for (int j = 1; j <= columnCount; j++) {
//                    columNames.add(rsmd.getColumnName(j));
//                }
                    columNames.add("id号");
                    columNames.add("演唱者姓名");
                    columNames.add("演唱会开始日期");
                    columNames.add("演唱会地址");
                    columNames.add("总票数");
                    columNames.add("剩余票数");
                    columNames.add("价格");
                results.add(columNames);
                if(!userSelect.resultSet.next()){
                    System.out.print("无此查询，请重新输入");
                    results.clear();
                }else{
                //处理查询结果集
                do{
                    List<String> row = new ArrayList<>();
                    for (int j = 1; j <= columnCount; j++) {
                        row.add(userSelect.resultSet.getString(j)+"\t");
                    }
                    results.add(row);
                }while (userSelect.resultSet.next());
                }

                for (List<String> row : results) {
                    for (String str : row) {
                        System.out.print(str + "\t\t");
                    }
                    System.out.println();
                }
                results.clear();

            }catch (Exception e){
                    System.out.println("您的输入有误,请重新输入");

            }finally{
                    Gy();
                }

        }
    }

}
